package com.fafa.pos.dao;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * Created by Bing on 2017/7/18.
 *
 */
public class DBUtil {

    private final String DBPath;
    private SQLiteDatabase db;

    public DBUtil(String dbPath) {
        this.DBPath = dbPath;
        db = SQLiteDatabase.openOrCreateDatabase(DBPath, null);
        initDB();
    }

    public SQLiteDatabase getDB() {
        if (db == null) {
            db = SQLiteDatabase.openOrCreateDatabase(DBPath, null);
        }
        return db;
    }

    private void initDB() {
        createTable();
        //initData();
    }

    private void createTable() {
        String createTableSql = "";


        //创建用户 表
        if (!tableExist("T_USERINFO")) {
            createTableSql = "create table T_USERINFO(" +
                    "USER_ID numeric(18) PRIMARY KEY NOT NULL," +           //用户ID
                    "USER_CODE varchar(50)  NOT  NULL," +                   //用户编码
                    "USER_NAME varchar(100)  NOT  NULL," +                  //用户名称
                    "PASSWORD varchar(100)  NOT  NULL," +                   //密码
                    "STATUS numeric(1)  NOT  NULL," +                       //状态1有效0无效
                    "VISIBLE numeric(1)  NOT  NULL," +                      //是否显示1是0否
                    "UPDATE_DATE varchar(20)  NOT  NULL)";                  //更新日期
            getDB().execSQL(createTableSql);
        }
        //创建会员分组 表
        if (!tableExist("T_VIPGROUPINFO")) {
            createTableSql = "create table T_VIPGROUPINFO(" +
                    "GROUP_ID numeric(18) PRIMARY KEY NOT NULL," +          //组ID
                    "GROUP_CODE varchar(50)  NOT  NULL," +                  //组编码
                    "GROUP_NAME varchar(100)  NOT  NULL," +                 //组名称
                    "LEVEL_ID numeric(18)  NOT  NULL," +                    //结款级别ID
                    "RATE numeric(18,6)  NOT  NULL," +                      //折扣
                    "STATUS numeric(1)  NOT  NULL," +                       //状态1有效0无效
                    "VISIBLE numeric(1)  NOT  NULL," +                      //是否显示1是0否
                    "UPDATE_DATE varchar(20)  NOT  NULL)";                  //更新日期
            getDB().execSQL(createTableSql);
        }

        //创建下载日期限制 表
        if (!tableExist("T_DATELIMITRULEINFO")) {
            createTableSql = "create table T_DATELIMITRULEINFO(" +
                    "LIMIT_ID numeric(18) PRIMARY KEY NOT NULL," +          //用户ID
                    "LIMIT_CODE varchar(50)  NOT  NULL," +                  //用户编码
                    "LIMIT_NAME varchar(100)  NOT  NULL," +                 //用户名称
                    "LIMIT_TYPE numeric(1)  NOT  NULL," +                   //限制类型
                    "ACCT_TYPE_ID numeric(18)  NOT  NULL," +                //帐户类型
                    "GROUP_ID numeric(18)  NOT  NULL," +                    //会员类型
                    "LINE_ID numeric(18)  NOT  NULL," +                     //服务线ID
                    "PERIOD_ID numeric(18)  NOT  NULL," +                   //时段ID
                    "LIMIT_AMT numeric(18,6)  NOT  NULL," +                 //金额
                    "LIMIT_NUM numeric(18)  NOT  NULL," +                   //次数
                    "STATUS numeric(1)  NOT  NULL," +                       //状态1有效0无效
                    "VISIBLE numeric(1)  NOT  NULL," +                      //是否显示1是0否
                    "UPDATE_DATE varchar(20)  NOT  NULL)";                  //更新日期
            getDB().execSQL(createTableSql);

            //LIMIT_TYPE =7, 离线金额限制
            //LIMIT_TYPE =8, 离线次数限制
        }



        //创建会员信息 表
        if (!tableExist("T_VIPINFO")) {
            createTableSql = "create table T_VIPINFO(" +
                    "VIP_ID numeric(18) PRIMARY KEY NOT NULL," +            //会员ID
                    "VIP_CODE varchar(50)  NOT  NULL," +                    //会员编号
                    "VIP_NAME varchar(100)  NOT  NULL," +                   //会员姓名
                    "VIP_STATUS numeric(1)  NOT  NULL," +                   //会员状态
                    "GROUP_ID numeric(18)  NOT  NULL," +                    //会员分组
                    "VISIBLE numeric(1)  NOT  NULL," +                      //是否显示1是0否
                    "UPDATE_DATE varchar(20)  NOT  NULL)";                  //更新日期
            getDB().execSQL(createTableSql);
        }
        //创建卡信息 表
        if (!tableExist("T_CARDINFO")) {
            createTableSql = "create table T_CARDINFO(" +
                    "CARD_ID numeric(18) PRIMARY KEY NOT NULL," +           //卡ID
                    "CARD_CODE varchar(50)  NOT  NULL," +                   //卡号
                    "CARD_STATUS numeric(18)  NOT  NULL," +                 //卡状态
                    "VIP_ID numeric(18)  NOT  NULL," +                      //会员ID
                    "VALID_DATE DateTime  NOT  NULL," +                     //有效期
                    "UPDATE_DATE varchar(20)  NOT  NULL)";                  //更新日期
            getDB().execSQL(createTableSql);
        }
        //创建POS信息 表
        if (!tableExist("T_POSINfO")) {
            createTableSql = "create table T_POSINfO(" +
                    "POS_ID numeric(18) PRIMARY KEY NOT NULL," +            //POSID
                    "POS_CODE varchar(100)  NOT  NULL," +                   //POS代码
                    "POS_NAME NVARCHAR(50)  NOT  NULL," +                   //POS名称
                    "IS_TERMINAL numeric(1)  NOT  NULL," +                  //是否机具
                    "STATUS numeric(1)  NOT  NULL," +                       //状态1有效0无效
                    "UPDATE_DATE varchar(20)  NOT  NULL)";                  //更新日期
            getDB().execSQL(createTableSql);
        }
        //创建消费明细 表
        if (!tableExist("T_CONSUMEINFO")) {
            createTableSql = "create table T_CONSUMEINFO(" +
                    "ID varchar(50)  PRIMARY KEY NOT NULL," +               //唯一标识ID
                    "CARD_CODE varchar(50) NOT NULL," +                     //消费卡号
                    "VIP_NAME varchar(50) NOT NULL," +                     //会员名称
                    "MONEY numeric(18,2)  NOT  NULL," +                     //消费金额
                    "DATE varchar(20)  NOT  NULL," +                        //消费日期
                    "ISUPLOAD numeric(1)  NOT  NULL,"+                      //是否已上传消费信息 1是0否
                    "ERRORMSG varchar(100)  NOT  NULL)";                    //上传失败的错误信息  记录最后一次

            getDB().execSQL(createTableSql);
        }


        //创建android config 表
        if (!tableExist("T_CONFIG")) {
            createTableSql = "create table T_CONFIG(" +
                    "KEY NVARCHAR(50) PRIMARY KEY NOT  NULL," +                 //key
                    "VALUE NVARCHAR(50)  NOT  NULL)";                           //value
            getDB().execSQL(createTableSql);

        }


    }

    private boolean tableExist(String tableName) {
        boolean result = false;
        if (tableName == null || "".equals(tableName)) {
            return false;
        }
        String sql = "select count(*) as c from sqlite_master where type='table' and name = '" + tableName.trim() + "'";
        Cursor cursor = null;
        try {
            cursor = getDB().rawQuery(sql, null);
            if (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
        }
        System.out.println(result ? tableName + "存在" : tableName + "不存在");
        return result;
    }

    public void clearTable(String tableName) {
        if (tableExist(tableName)) {

            //清空表中的数据
            getDB().delete(tableName, null, null);
            System.out.println(tableName + "已清空");

        }
    }

    public void clear(){
        clearTable("T_USERINFO");
        clearTable("T_VIPGROUPINFO");
        clearTable("T_DATELIMITRULEINFO");
        clearTable("T_VIPINFO");
        clearTable("T_CARDINFO");
        clearTable("T_POSINfO");
        clearTable("T_CONSUMEINFO");
        clearTable("T_CONFIG");

    }

    public void deleteTable(String tableName) {
        if (tableExist(tableName)) {
            String sql = "DROP TABLE " + tableName;
            getDB().execSQL(sql);

            //清空表中的数据
            //getDB().delete(tableName,null,null);
            System.out.println(tableName + "已删除");

        }
    }

    public void close() {
        if (db != null) {
            db.close();
            db = null;
        }

    }


    private void initData() {

    }


}
